/**
 * 
 */
package com.cqcis.uip.module.salary.oper.dao;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.cqcis.uip.base.framework.UIPServiceException;
import com.cqcis.uip.base.framework.utils.JdbcUtil;
import com.cqcis.uip.base.framework.utils.StringUtil;
import com.cqcis.uip.module.salary.oper.SalaryConstants;
import com.cqcis.uip.module.salary.oper.domain.CourseRealRecord;
import com.cqcis.uip.module.salary.oper.domain.SalaryCourseTimesDetail;
import com.cqcis.uip.module.salary.oper.domain.extend.Trusteeship;

/**
 * @author 李世军
 *
 * 创建于 2012-3-20 下午03:02:16
 */
public class CourseRealRecordDao extends JdbcDaoSupport {
	private static Log log = LogFactory.getLog(CourseRealRecordDao.class);
	private String SELECT_SQL = " select create_time,course_id,study_type,student_id,grade_id,begin_time,end_time,course_hour,attend,state,remark,company_id,teacher_id,student_manager_id,auditor_id " +
			" from resourcecontrol_courserealrecord where state=2 and create_time>=? and create_time<=? and teacher_id=? " ;
	
	public List<CourseRealRecord> find(String teacheId){
		return JdbcUtil.queryForList(this, CourseRealRecord.class, SELECT_SQL, SalaryConstants.SALARY_BEGIN_DATE, SalaryConstants.SALARY_END_DATE,teacheId) ;
	}
	
	// 获取单价
	@Deprecated
	private String TC_SQL = " select id from customercontrol_trainingcontract where student_id=? and state in(1,4) and contract_type !=3 limit 0,1 " ;
	@Deprecated
	private String DTL_SQL = " select current_price from customercontrol_trainingcontractdetail where training_contract_id=? and grade_id=? and course_id=? and study_type=? " ;
	
	@Deprecated
	public float getPrice(int studentId, int gradeId, int courseId, int studyType ){
		String tcId = JdbcUtil.queryForObject(this, String.class, TC_SQL, studentId) ;
		return JdbcUtil.queryForObject(this, Float.class, DTL_SQL, tcId,gradeId,courseId,studyType) ;
	}
	
	
	private String TRUSTEESHIP_SQL = " select student_id,grade_id,course_id, sum(course_hour) as days " 
									+" from resourcecontrol_courserealrecord where course_id in "
									+" (select id from resourcecontrol_course where course_type=2) " +
									"  and teacher_id=? " 
									+" group by student_id, grade_id, course_id  " ;
	/**
	 * 教师托管
	 * @param employeId
	 * @return
	 */
	public List<Trusteeship> findTrusteeship(String employeId){
		return JdbcUtil.queryForList(this, Trusteeship.class, TRUSTEESHIP_SQL, employeId) ;
	}
	
	
	private String TRUSTEESHIP_SM_SQL = " select student_id,grade_id,course_id, sum(course_hour) as days " 
		+" from resourcecontrol_courserealrecord where course_id in "
		+" (select id from resourcecontrol_course where course_type=2) " +
		"  and student_manager_id=? " 
		+" group by student_id, grade_id, course_id  " ;
	/**
	 * 学管师托管
	 * @param employeId
	 * @return
	 */
	public List<Trusteeship> findTrusteeshipSm(String employeId){
		return JdbcUtil.queryForList(this, Trusteeship.class, TRUSTEESHIP_SM_SQL, employeId) ;
	}
	
	
	
	
	//////////////////////////////////////////////////学管师,课时提成 ////////////////////////////////////////////////////////
	private String SELECT_STUDENTMANAGER_SQL = " select create_time,course_id,study_type,student_id,grade_id,begin_time,end_time,course_hour,attend,state,remark,company_id,teacher_id,student_manager_id,auditor_id " +
		" from resourcecontrol_courserealrecord where state=2 and create_time>=? and create_time<=? and student_manager_id=? " ;

	public List<CourseRealRecord> findStudnetManager(String studentManagerId){
		return JdbcUtil.queryForList(this, CourseRealRecord.class, SELECT_STUDENTMANAGER_SQL, SalaryConstants.SALARY_BEGIN_DATE, SalaryConstants.SALARY_END_DATE,studentManagerId) ;
	}
	
	
	public static void main(String[] args) {
		System.out.println(STUDENT_MANAGER_TASK_SQL);
	}
	
	private static String STUDENT_MANAGER_TASK_SQL = " select" 
							+"("
							+"	(select course_expect+course_new from financecontrol_companytask where company_id=? and month>=? and month<=?)" 
							+"	/"
							+"	(select count(*) from employecontrol_employe where office_id="+SalaryConstants.OFFICE_30+" and company_id=? )"
							+")"
							+"from dual  " ;
	/**
	 * 获取学管师课时任务量
	 * @param compnayId
	 * @return
	 */
	public float getStudentManagerTask(String compnayId){
		return JdbcUtil.queryForObject(this, Float.class, STUDENT_MANAGER_TASK_SQL, compnayId, SalaryConstants.SALARY_BEGIN_DATE,SalaryConstants.SALARY_END_DATE, compnayId) ;
	}
	
	private static String STUDENT_CONTRACT_SQL = " select id from customercontrol_trainingcontract where student_id=? and state in(1,4) and begin_time<? and end_time>? limit 0,1 " ;
	private static String COURSE_PRICE_SQL = " select current_price from customercontrol_trainingcontractdetail where training_contract_id =? and grade_id=? and course_id=?  " ;
	
	/**
	 * 获取课时对应价格
	 * @param studnetId
	 * @param gradeId
	 * @param courseId
	 * @return
	 */
	public float getCoursePrice(int studnetId,String create_time, int gradeId, int courseId ){
		if(studnetId==0){
			return 0F ;
		}
		
		String contractId = JdbcUtil.queryForObject(this, String.class, STUDENT_CONTRACT_SQL, studnetId, create_time,create_time) ;
		if(StringUtil.hasText(contractId)){
			return JdbcUtil.queryForObject(this, Float.class, COURSE_PRICE_SQL, contractId, gradeId, courseId) ;
		}
		log.warn("获取学管师-主管课时单价异常:courserealrecord.studnetId="+studnetId+",courserealrecord.create_time="+create_time+"    无对应合同记录.") ;
		return 0F ;	
	}
	
	
	////////////////////////////////////////////////学管师-主管,课时提成 ////////////////////////////////////////////////////////
	private String SELECT_STUDENTMANAGER_UP_SQL = " select create_time,course_id,study_type,student_id,grade_id,begin_time,end_time,course_hour,attend,state,remark,company_id,teacher_id,student_manager_id,auditor_id " +
	" from resourcecontrol_courserealrecord where state=2 and create_time>=? and create_time<=? and student_manager_id=? " ;
	
	public List<CourseRealRecord> findStudnetManagerUp(String studentManagerUpId){
		return JdbcUtil.queryForList(this, CourseRealRecord.class, SELECT_STUDENTMANAGER_UP_SQL, SalaryConstants.SALARY_BEGIN_DATE, SalaryConstants.SALARY_END_DATE,studentManagerUpId) ;
	}
	
	private static String STUDENT_MANAGER_UP_TASK_SQL = " select" 
		+"("
		+"	(select course_expect+course_new from financecontrol_companytask where company_id=? and month>=? and month<=?)" 
		+")"
		+"from dual  " ;
	
	/**
	* 获取主管课时任务量
	* @param compnayId
	* @return
	*/
	public float getStudentManagerUpTask(String compnayId){
		return JdbcUtil.queryForObject(this, Float.class, STUDENT_MANAGER_UP_TASK_SQL, compnayId, SalaryConstants.SALARY_BEGIN_DATE,SalaryConstants.SALARY_END_DATE) ;
	}
	
	private static String OFFICE_EMPLOYE_SQL = " select id from employecontrol_employe where  company_id=? and office_id=30 " ;
	/**
	 * 获取分公司下所有学管师
	 * @param companyId
	 * @return
	 */
	public List<String> findDownStudentManager(String companyId){
		return JdbcUtil.queryForList(this, String.class, OFFICE_EMPLOYE_SQL, companyId) ;
	}
	
	
	////////////////////////////////////////////////全职教师,课时提成 ////////////////////////////////////////////////////////
	
	private static String GRADE_COURSE_SQL = " select grade_id,sum(course_hour) as course_times, study_type from resourcecontrol_courserealrecord where state=2 and study_type=? and teacher_id=?  and create_time>=? and create_time<=?  group by grade_id " ;
	/**
	 * 根据学习试获取年级课时
	 * @param studyType
	 * @return SalaryCourseTimesDetail.grade_id, SalaryCourseTimesDetail.course_times
	 */
	public List<SalaryCourseTimesDetail> getGradeCourseTimes(String teacherId, String studyType){
		return JdbcUtil.queryForList(this, SalaryCourseTimesDetail.class, GRADE_COURSE_SQL, studyType, teacherId, SalaryConstants.SALARY_BEGIN_DATE, SalaryConstants.SALARY_END_DATE) ;
	}
	
	private static String JZ_TEACHER_PROCE_SQL_A = " select " ;
	private static String JZ_TEACHER_PROCE_SQL_B = "  from employecontrol_employejzteachersalary where employe_id=? limit 0,1   " ;
	public float getJzTeacherPrice(String employeId, int gradeId){
		String field = "" ;
		
		if(1==gradeId)
			field = "x1" ;
		else if(2==gradeId)
			field = "x2" ;
		else if(3==gradeId)
			field = "x3" ;
		else if(4==gradeId)
			field = "x4" ;
		else if(5==gradeId)
			field = "x5" ;
		else if(6==gradeId)
			field = "x6" ;
		else if(7==gradeId)
			field = "c1" ;
		else if(8==gradeId)
			field = "c2" ;
		else if(9==gradeId)
			field = "c3" ;
		else if(10==gradeId)
			field = "g1" ;
		else if(11==gradeId)
			field = "g2" ;
		else if(12==gradeId)
			field = "g3" ;
		else 
			throw new UIPServiceException("获取兼职教师课时单价错误,年级ID："+ gradeId) ;
		
		
		Float p = JdbcUtil.queryForObject(this, Float.class, JZ_TEACHER_PROCE_SQL_A+field+JZ_TEACHER_PROCE_SQL_B, employeId) ;
		
		
		if(p==null){
			throw new UIPServiceException("兼职教师:"+employeId+"	年级:"+gradeId+"		单价未配置") ;
		}
		return p ;
		
	}
	
}


